package com.zzjx.db;

import com.alibaba.druid.pool.DruidDataSource;
import com.redxun.common.base.entity.GridHeader;
import com.redxun.common.utils.SpringUtil;
import com.zzjx.datasource.DataSourceContextHolder;
import com.zzjx.datasource.DataSourceUtil;
import com.zzjx.dboperator.ITableMeta;
import com.zzjx.dboperator.TableMetaContext;
import com.zzjx.dboperator.model.Column;
import com.zzjx.dboperator.model.Table;
import org.owasp.esapi.ESAPI;
import org.owasp.esapi.codecs.Codec;
import org.owasp.esapi.codecs.OracleCodec;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.jdbc.support.rowset.SqlRowSetMetaData;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DbUtil {

    private static OracleCodec ORACLE_CODEC = new OracleCodec();
    private static MySQLCodec MYSQL_CODEC = new MySQLCodec(MySQLCodec.Mode.STANDARD);
    private static SqlServerCodec SQLSERVER_CODEC = new SqlServerCodec();


    /**
     * 对SQL进行编码
     *
     * @param sql
     * @return
     */
    public static String encodeSql(String sql) {
        String type = DataSourceUtil.getCurrentDbType();
        Codec codec = null;
        if (SQLConst.DB_MYSQL.equals(type)) {
            codec = MYSQL_CODEC;
        } else if (SQLConst.DB_ORACLE.equals(type)) {
            codec = ORACLE_CODEC;
        } else if (SQLConst.DB_SQLSERVER.equals(type)) {
            codec = SQLSERVER_CODEC;
        } else if (SQLConst.DB_DM.equals(type)) {
            codec = ORACLE_CODEC;
        }

        sql = ESAPI.encoder().encodeForSQL(codec, sql);
        return sql;
    }

    /**
     * 自定义列表设计，大数据量情况下，根据不同数据库处理分页问题。
     *
     * @param sql
     * @return
     */
    public static String preHandleSql(String sql) {
        String type = DataSourceUtil.getCurrentDbType();
        if (SQLConst.DB_MYSQL.equals(type)) {
            sql += " limit 5";
        } else if (SQLConst.DB_ORACLE.equals(type)) {
            sql = "select * from (" + sql + ") where rownum < 5 order by rownum asc";
        } else if (SQLConst.DB_SQLSERVER.equals(type)) {
            sql = sql.toLowerCase().replaceFirst("select", "select top 5 ");
        } else if (SQLConst.DB_DM.equals(type)) {
            sql += " limit 0,5";
        }
        return sql;
    }

    public static List<GridHeader> getGridHeader(String sql) throws IllegalAccessException, NoSuchFieldException, SQLException {
        JdbcTemplate jdbcTemplate = SpringUtil.getBean(JdbcTemplate.class);
        ITableMeta iTableMeta = TableMetaContext.getCurrentTableMeta();
        iTableMeta.setJdbcTemplate(jdbcTemplate);
        List<GridHeader> headers = new ArrayList<>();
        String orgSql = sql;
        try {
            SqlRowSet rowSet = jdbcTemplate.queryForRowSet(sql);
            SqlRowSetMetaData metaData = rowSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            //若为单独的表，可以读取其注释字段信息
            sql = sql.toUpperCase();
            int lastFromIndex = sql.indexOf(" FROM ");
            Map<String, Column> columnMap = new HashMap<>();
            if (lastFromIndex > 0) {
                String fromSubSql = orgSql.substring(lastFromIndex + 5).trim();
                int tableIndex = fromSubSql.indexOf(" ");
                String tableName = null;
                if (tableIndex > 0) {
                    tableName = fromSubSql.substring(0, tableIndex).trim();
                } else {
                    tableName = fromSubSql;
                }
                Table table = iTableMeta.getTableByName(tableName);
                List<Column> columns = table.getColumnList();
                for (Column c : columns) {
                    columnMap.put(c.getFieldName(), c);
                }
            }
            for (int i = 1; i <= columnCount; i++) {
                String fieldName = metaData.getColumnLabel(i);
                String fieldType = metaData.getColumnTypeName(i);

                GridHeader header = new GridHeader();
                header.setFieldName(fieldName);
                header.setFieldType(fieldType);
                header.setLength(metaData.getColumnDisplaySize(i));
                header.setPrecision(metaData.getPrecision(i));

                Column c = columnMap.get(fieldName);
                if (c != null) {
                    header.setFieldLabel(c.getComment());
                    header.setIsNull(new Boolean(c.getIsNull()).toString());
                }
                header.setDbFieldType(metaData.getColumnType(i));
                headers.add(header);
            }
            return headers;
        } catch (Exception e) {
            Connection conn = null;
            ResultSet resultSet = null;
            DataSource dataSource = null;
            try {
                String ds = DataSourceContextHolder.getDataSource();
                dataSource = DataSourceUtil.getDataSourcesByAlias(ds);
                conn = DataSourceUtils.getConnection(dataSource);

                PreparedStatement preparedStatement = conn.prepareStatement(orgSql);
                resultSet = preparedStatement.executeQuery();

                ResultSetMetaData meta = resultSet.getMetaData();
                int columnCount = meta.getColumnCount();
                for (int i = 1; i <= columnCount; i++) {
                    GridHeader gridHeader = new GridHeader();

                    int columnType = meta.getColumnType(i);
                    String columnName = meta.getColumnName(i);
                    String columnTypeName = meta.getColumnTypeName(i);
                    String columnLabel = meta.getColumnLabel(i);


                    gridHeader.setFieldLabel(columnLabel);
                    gridHeader.setDbFieldType(columnType);
                    gridHeader.setFieldName(columnName);
                    gridHeader.setFieldType(columnTypeName);
                    gridHeader.setLength(meta.getColumnDisplaySize(i));
                    gridHeader.setPrecision(meta.getPrecision(i));
                    gridHeader.setIsNull(meta.isNullable(i) == 0 ? "false" : "true");

                    headers.add(gridHeader);
                }
            } catch (Exception e1) {
                e1.printStackTrace();
            } finally {
                JdbcUtils.closeResultSet(resultSet);
                DataSourceUtils.doReleaseConnection(conn, dataSource);
            }
        }
        return headers;
    }

}
